package zuoye881;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import zuoye.DBUtil;

public class StudentDao {
	public int add(Student s) {
		Connection con = DBUtil.getConnection();
		String sql = "insert into student(stuno,sname,sex,telephone,qq,wechat,gradeid,fromcity,birthday,address,pass) values(?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, s.getStuno());
			st.setString(2, s.getSname());
			st.setString(3, s.getSex());
			st.setLong(4, s.getTelephone());
			st.setLong(5, s.getQq());
			st.setString(6, s.getWechat());
			st.setInt(7, s.getGradeid());
			st.setString(8, s.getFromcity());
			st.setDate(9, new java.sql.Date(s.getBirthday().getTime()));
			st.setString(10, s.getAddr());
			st.setLong(11, s.getPass());
			int a = st.executeUpdate();
			return a;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(st);
			DBUtil.close(con);
		}
		return 0;
	}

	public int update(Student a) {
		Connection con = DBUtil.getConnection();
		String sql = "update student set stuno=?,sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,fromcity=?,birthday=?,address=?,pass=? where stuno=?";
		PreparedStatement st = null;
		try {
			int index = 0;
			st = con.prepareStatement(sql);
			st.setInt(index++, a.getStuno());
			st.setString(index++, a.getSname());
			st.setString(index++, a.getSex());
			st.setLong(index++, a.getTelephone());
			st.setLong(index++, a.getQq());
			st.setString(index++, a.getWechat());
			st.setInt(index++, a.getGradeid());
			st.setString(index++, a.getFromcity());
			st.setDate(index++, new java.sql.Date(a.getBirthday().getTime()));
			st.setString(index++, a.getAddr());
			st.setLong(index++, a.getPass());
			st.setInt(index++, a.getStuno());
			int b = st.executeUpdate();
			return b;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(st);
			DBUtil.close(con);
		}
		return 0;
	}

	public int dele(long id) {
		Connection con = DBUtil.getConnection();
		String sql = "delete from student where stuno=?";
		PreparedStatement st = null;
		try {
			st = con.prepareCall(sql);
			st.setLong(1, id);
			int b = st.executeUpdate();
			return b;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(st);
			DBUtil.close(con);
		}
		return 0;
	}

	public Student findstu(long id) {
		Connection con = DBUtil.getConnection();
		String sql = "select * from student where stuno=?";
		PreparedStatement st = null;
		ResultSet rs = null;
		Student s = new Student();
		try {
			st = con.prepareStatement(sql);
			st.setLong(1, id);
			rs = st.executeQuery();
			if (rs.next()) {
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getNString("sex"));
				s.setTelephone(rs.getLong("telephone"));
				s.setQq(rs.getLong("qq"));
				s.setWechat(rs.getNString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setFromcity(rs.getNString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				s.setAddr(rs.getString("address"));
				s.setPass(rs.getLong("pass"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(st);
			DBUtil.close(con);
		}
		return s;
	}

	public Student login(String userName, String pass) {
		Connection con = DBUtil.getConnection();
		String sql = "select * from student where sname=? and pass=?";
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, userName);
			st.setString(2, pass);
			rs = st.executeQuery();
			if (rs.next()) {
				Student s = new Student();
				s.setStuno(rs.getInt("stuno"));
				s.setSname(rs.getString("sname"));
				s.setSex(rs.getNString("sex"));
				s.setTelephone(rs.getLong("telephone"));
				s.setQq(rs.getLong("qq"));
				s.setWechat(rs.getNString("wechat"));
				s.setGradeid(rs.getInt("gradeid"));
				s.setFromcity(rs.getNString("fromcity"));
				s.setBirthday(rs.getDate("birthday"));
				s.setAddr(rs.getString("address"));
				s.setPass(rs.getLong("pass"));
				return s;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(st);
			DBUtil.close(con);
		}
		return null;
	}

	public List<Student> paging(int pageSize, int pageNo) {
		int start = (pageNo - 1) * pageSize;
		int end = pageNo * pageSize;
		Connection con = DBUtil.getConnection();
		String sql = "select * from student";
		PreparedStatement st = null;
		ResultSet rs = null;
		List<Student> s = new ArrayList();
		try {
			st = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			st.setMaxRows(end);
			rs = st.executeQuery();
			if (start > 0) {
				rs.absolute(start);
			}
			while (rs.next()) {
				Student a = new Student();
				a.setStuno(rs.getInt("stuno"));
				a.setSname(rs.getString("sname"));
				a.setSex(rs.getNString("sex"));
				a.setTelephone(rs.getLong("telephone"));
				a.setQq(rs.getLong("qq"));
				a.setWechat(rs.getNString("wechat"));
				a.setGradeid(rs.getInt("gradeid"));
				a.setFromcity(rs.getNString("fromcity"));
				a.setBirthday(rs.getDate("birthday"));
				a.setAddr(rs.getString("address"));
				a.setPass(rs.getLong("pass"));
				s.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(st);
			DBUtil.close(con);
		}
		return s;
	}
}
